package Excel;

import Excel.Model.ColumnModel;
import Excel.Model.SheetModel;
import Excel.Model.WorkBookModel;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Logger;

public class ExcelOperator {

    public void setWorkBookModel(WorkBookModel workBookModel) {
        this.workBookModel = workBookModel;
    }

    public ExcelOperator() {
        this.xssfWorkbook = new XSSFWorkbook();
    }

    private String path;
    private XSSFWorkbook xssfWorkbook;
    private XSSFCellStyle style;
    private XSSFFont font;
    private WorkBookModel workBookModel;

    public String getPath() {
        return path;
    }

    public void setPath(String path) {
        this.path = path;
    }

    /**
     * 每工作表最大数据量
     */
    private final static int MAX_DATA_COUNT = 100000;
    /**
     * 所有字体名称
     */
    private final static String FONT_NAME = "宋体";
    /**
     * 所有字体颜色
     */
    private final static short FONT_COLOR = HSSFColor.BLACK.index;
    /**
     * 说明字体颜色
     */
    private final static short REMARK_FONT_COLOR = HSSFColor.BLUE.index;
    /**
     * 标题，字体大小
     */
    private final static short TITLE_FONT_SIZE = 30;
    /**
     * 列，字体大小
     */
    private final static short Column_TITLE_FONT_SIZE = 14;
    /**
     * 数据，字体大小
     */
    private final static short DATA_FONT_SIZE = 13;
    /**
     * 说明，字体大小
     */
    private final static short REMARK_FONT_SIZE = 11;
    /**
     * 用户名及时间字体大小
     */
    private final static short USE_TIME_FONT_SIZE = 12;
    /**
     * 标题，背景色
     */
    private final static short TITLE_COLUMN_BACKCOLOR = HSSFColor.GREY_40_PERCENT.index;
    /**
     * 奇行，背景色
     */
    private final static short ODD_ROW_BACKCOLOR = HSSFColor.GREY_25_PERCENT.index;

    private XSSFCellStyle getColumnTitleFirst() {
        return getStyle(true
                , (byte) 0
                , Column_TITLE_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , (byte) 2
                , 0);
    }

    private XSSFCellStyle getColumnTitleMinute() {
        return getStyle(true
                , (byte) 0
                , Column_TITLE_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , (byte) 1
                , 0);
    }

    private XSSFCellStyle getColumnTitleLast() {
        return getStyle(true
                , (byte) 0
                , Column_TITLE_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , (byte) 3
                , 0);
    }

    private XSSFCellStyle getUse() {
        return getStyle(true
                , (byte) -1
                , USE_TIME_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , (byte) 22
                , 0);
    }

    private XSSFCellStyle getDate() {
        return getStyle(true
                , (byte) 1
                , USE_TIME_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , (byte) 22
                , 0);
    }

    private XSSFCellStyle getRemark() {
        return getStyle(true
                , (byte) -1
                , REMARK_FONT_SIZE
                , FONT_NAME
                , REMARK_FONT_COLOR
                , (byte) 23
                , 0);
    }

    /**
     * 得到数据风格
     *
     * @param textSite 文本位置
     * @param rowId    数据行号
     * @return
     */
    private XSSFCellStyle getDataStyle(byte textSite, byte boardType, int rowId) {
        return getStyle(true
                , textSite
                , DATA_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , boardType
                , rowId);
    }

    /**
     * 得到标题风格
     *
     * @return 标题风格
     */
    private XSSFCellStyle getTitleStyle() {
        return getStyle(true
                , (byte) 0
                , TITLE_FONT_SIZE
                , FONT_NAME
                , FONT_COLOR
                , (byte) 88
                , 0);
    }

    /**
     * 得到风格，以矩形标识风格
     * 2   1   3
     * -4   0   4
     * -2  -1  -3
     * 88:标题栏、22：用户与导出时间、23：说明
     *
     * @param backWardSign 是否有边框
     * @param textSite     文本位置
     * @param fontSize     字体大小
     * @param fontName     字体名称
     * @param fontColor    字体颜色
     * @param boardType    边框类型
     * @param rowId        数据行号，小于1，不使用
     * @return 风格
     */
    private XSSFCellStyle getStyle(boolean backWardSign
            , byte textSite
            , short fontSize
            , String fontName
            , short fontColor
            , byte boardType
            , int rowId
    ) {
        this.style = this.xssfWorkbook.createCellStyle();
        this.font = this.xssfWorkbook.createFont();
        this.font.setFontName(fontName);//设置字体名称
        this.font.setFontHeightInPoints(fontSize);//设置字号
        this.font.setColor(fontColor);//设置字体颜色
//        font.setUnderline(FontFormatting.U_SINGLE);//设置下划线
//        font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标
//        font.setStrikeout(true);//设置删除线

        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//垂直居中
        switch (textSite) {
            case -1:
                style.setAlignment(XSSFCellStyle.ALIGN_LEFT);//水平居左
                break;
            case 0:
                style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
                break;
            case 1:
                style.setAlignment(XSSFCellStyle.ALIGN_RIGHT);//水平居右
                break;
            default:
                style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//水平居中
                break;
        }
        if (backWardSign) {
            style.setTopBorderColor(HSSFColor.BLACK.index);//上边框颜色
            style.setBottomBorderColor(HSSFColor.BLACK.index);//下边框颜色
            style.setLeftBorderColor(HSSFColor.BLACK.index);//左边框颜色
            style.setRightBorderColor(HSSFColor.BLACK.index);//右边框颜色
            switch (boardType) {
                case 1://1 ：中上
                    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    style.setFillForegroundColor(TITLE_COLUMN_BACKCOLOR);//设置图案颜色CellStyle.SOLID_FOREGROUND
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    this.font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    break;
                case 0://0 : 中心
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    if (rowId > 0 && rowId % 2 == 1) {
                        style.setFillForegroundColor(ODD_ROW_BACKCOLOR);//设置图案背景色
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    }
                    break;
                case -1://-1：中下
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    if (rowId > 0 && rowId % 2 == 1) {
                        style.setFillForegroundColor(ODD_ROW_BACKCOLOR);//设置图案背景色
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    }
                    break;
                case 2://2 ：左上
                    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    style.setFillForegroundColor(TITLE_COLUMN_BACKCOLOR);//设置图案颜色CellStyle.SOLID_FOREGROUND
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    this.font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    break;
                case -4://-4：左中
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    if (rowId > 0 && rowId % 2 == 1) {
                        style.setFillForegroundColor(ODD_ROW_BACKCOLOR);//设置图案背景色
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    }
                    break;
                case -2://-2：左下
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    if (rowId > 0 && rowId % 2 == 1) {
                        style.setFillForegroundColor(ODD_ROW_BACKCOLOR);//设置图案背景色
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    }
                    break;
                case 3://3 ：右上
                    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
                    style.setFillForegroundColor(TITLE_COLUMN_BACKCOLOR);//设置图案颜色CellStyle.SOLID_FOREGROUND
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    this.font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    break;
                case 4://4 ：右中
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
                    if (rowId > 0 && rowId % 2 == 1) {
                        style.setFillForegroundColor(ODD_ROW_BACKCOLOR);//设置图案背景色
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    }
                    break;
                case -3://-3：右下
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);//右边框
                    if (rowId > 0 && rowId % 2 == 1) {
                        style.setFillForegroundColor(ODD_ROW_BACKCOLOR);//设置图案背景色
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    }
                    break;
                case 88://标题
                    style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
                    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
                    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
                    style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
                    style.setFillForegroundColor(TITLE_COLUMN_BACKCOLOR);//设置图案颜色CellStyle.SOLID_FOREGROUND
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);//设置图案样式
                    this.font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    break;
                case 22://用户与时间
                    this.font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    break;
                case 23://说明
                    style.setBorderTop(HSSFCellStyle.NO_FILL);//上边框
                    style.setBorderBottom(HSSFCellStyle.NO_FILL);//下边框
                    style.setBorderLeft(HSSFCellStyle.NO_FILL);//左边框
                    style.setBorderRight(HSSFCellStyle.NO_FILL);//右边框
                    this.font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
                    break;
                default:
                    break;
            }
        }
        style.setFont(this.font);
        return style;
    }

    private <T> T getMyData(Object data, Class<T> tClass) {
        String name = tClass.getName();
        T resultType;
        if ("String".equals(name)) {
            resultType = (T) String.valueOf(data);
        } else if ("int".equals(name)) {
            resultType = (T) Integer.valueOf((Integer) data);
        } else if ("double".equals(name)) {
            resultType = (T) Double.valueOf((Double) data);
        } else {
            resultType = (T) String.valueOf(data);
        }
        return resultType;
    }

    /**
     * 检查数据合法性
     *
     * @return 是否合法
     */
    private boolean checkValid() {
        boolean okSign = true;
        String errorInfo = null;
        Logger logger = Logger.getLogger("check Valid:");
//        文件名，是否为空；
        if (this.workBookModel.getFileName().isEmpty()) {
            okSign = false;
            errorInfo = "文件名，不能为空！";
        }

//        Sheet集合长度是否为零，集合，是否为空；
        if (okSign && this.workBookModel.getSheetModels().length == 0) {
            okSign = false;
            errorInfo = "没有找到工作表！";
        }

//        Sheet名是否为空，是否重复；
        if (okSign) {
            for (int i = 0; i < this.workBookModel.getSheetModels().length; i++) {
                if (this.workBookModel.getSheetModels()[i].getSheetName().isEmpty()) {
                    okSign = false;
                    errorInfo = String.format("第%d个工作表的名称为空！", i + 1);
                    break;
                }
            }
        }
//        列长度，是否为非零;
        if (okSign) {
            for (int i = 0; i < this.workBookModel.getSheetModels().length; i++) {
                if (this.workBookModel.getSheetModels()[i].getColumnModels().length == 0) {
                    okSign = false;
                    errorInfo = String.format("第%d个工作表的列头长度为零空！", i + 1);
                    break;
                }
            }
        }
//        列头，是否为空；
        if (okSign) {
            for (int i = 0; i < this.workBookModel.getSheetModels().length; i++) {
                for (int j = 0; j < this.workBookModel.getSheetModels()[i].getColumnModels().length; j++) {
                    if (this.workBookModel.getSheetModels()[i].getColumnModels()[j].getColumnName().isEmpty()) {
                        okSign = false;
                        errorInfo = String.format("第%d个工作表的列头名为空！", i + 1);
                        break;
                    }
                }

            }
        }
//        列长度，数据列长与合计数据长度(若有合计栏的前提下)，是否一致；
        if (okSign) {
            for (int i = 0; i < this.workBookModel.getSheetModels().length; i++) {
                if (this.workBookModel.getSheetModels()[i].getColumnModels().length != this.workBookModel.getSheetModels()[i].getDataArray()[0].length) {
                    okSign = false;
                    errorInfo = String.format("第%d个工作表的列头长度与数据列长度不一致，不允许导出！", i + 1);
                    break;
                }
                if (this.workBookModel.getSheetModels()[i].isTotalSign() && this.workBookModel.getSheetModels()[i].getColumnModels().length != this.workBookModel.getSheetModels()[i].getTotalArray().length) {
                    okSign = false;
                    errorInfo = String.format("第%d个工作表的列头长度与合计列长度不一致，不允许导出！", i + 1);
                    break;
                }
            }
        }
//        某Sheet数据量，是否为空；
        if (okSign) {
            for (int i = 0; i < this.workBookModel.getSheetModels().length; i++) {
                for (int j = 0; j < this.workBookModel.getSheetModels()[i].getDataArray().length; j++) {
                    final Object[] objects = this.workBookModel.getSheetModels()[i].getDataArray()[j];
                    if (objects.length == 0) {
                        okSign = false;
                        errorInfo = String.format("第%d个工作表的数量为零，不允许导出！", i + 1);
                        break;
                    }
//        数据量是否超越Sheet最大限制；
                    if (objects.length > MAX_DATA_COUNT) {
                        okSign = false;
                        errorInfo = String.format("第%d个工作表的数据超过%d条，不允许导出！", i + 1, MAX_DATA_COUNT);
                        break;
                    }
                }
            }
        }

//        指定合计栏，但合计数据为空；
        if (okSign) {
            for (int i = 0; i < this.workBookModel.getSheetModels().length; i++) {
                if (this.workBookModel.getSheetModels()[i].isTotalSign() && this.workBookModel.getSheetModels()[i].getTotalArray().length == 0) {
                    okSign = false;
                    errorInfo = String.format("第%d个工作表的合计数据为空，不允许导出！", i + 1);
                    break;
                }
            }
        }

        if (!okSign)
            logger.info(errorInfo);
        return okSign;
    }

    /**
     * 导出EXCEL
     *
     * @param tClass   String:返回实体文件所在路径、Void：返回图片流
     * @param response Http请求
     * @param <T>      泛型
     * @return
     * @throws IOException
     */
    public <T> T toExcel(Class<T> tClass, HttpServletResponse response) throws IOException {
        if (!checkValid())
            return null;

        final int freezeRow = 4;
        final SheetModel[] sheetModels = this.workBookModel.getSheetModels();
        final String useName = this.workBookModel.getUseName();
        final String fileName = this.workBookModel.getFileName();
        final boolean noSign = this.workBookModel.isNoSign();
        for (int i = 0; i < sheetModels.length; i++) {
            final boolean freezeFirstSign = sheetModels[i].isFreezeFirstSign();
            final String sheetName = sheetModels[i].getSheetName();
            final String remark = sheetModels[i].getRemark();
            final ColumnModel[] columnModels = sheetModels[i].getColumnModels();
            final Object[] totalArray = sheetModels[i].getTotalArray();
            final boolean totalSign = sheetModels[i].isTotalSign();
            final Object[][] dataArray = sheetModels[i].getDataArray();
            final String sheetTitle = sheetModels[i].getSheetTitle();
            final byte titleX = sheetModels[i].getTitleX();
            final byte titleY = sheetModels[i].getTitleY();
            byte dataLength = (byte) (columnModels.length + (noSign ? 1 : 0));

            //定义坐标
            final int useX = titleX;
            final int useY = titleY + 2;
            final int dateX = titleX + dataLength - 1;
            final int colX = titleX;
            final int colY = titleY + 3;
            final int dataX = titleX;
            final int dataY = titleY + 4;

            //创建新的Sheet页
            XSSFSheet sheet = this.xssfWorkbook.createSheet(sheetName);

            // 冻结首行
            // 前两个参数是用来拆分的列数和行数
            // 后两个参数是下面窗口的可见象限
            // 第三个参数是右边区域可见的左边列数
            // 第四个参数是下面区域可见的首行
            if (freezeFirstSign) {
                sheet.createFreezePane(0, freezeRow, 1, freezeRow);
            }

            //写入标题，合并单元格
            final int remarkX = titleX;
            XSSFRow rowTitle = sheet.createRow(titleY);
            XSSFCellStyle titleStyle = getTitleStyle();
            for (byte index = 0; index < dataLength; index++) {
                XSSFCell titleCell = rowTitle.createCell(titleX + index);
                if (index == 0) {
                    titleCell.setCellValue(sheetTitle);
                }
                titleCell.setCellStyle(titleStyle);
            }
            CellRangeAddress region = new CellRangeAddress(titleY, titleY, titleX, titleX + dataLength - 1);
            sheet.addMergedRegion(region);

            //写入用户与导出时间
            final XSSFCellStyle useStyle = getUse();
            final XSSFCellStyle dateStyle = getDate();
            final XSSFCellStyle columnTitleFirst = getColumnTitleFirst();
            final XSSFCellStyle columnTitleMinute = getColumnTitleMinute();
            final XSSFCellStyle columnTitleLast = getColumnTitleLast();
            XSSFRow rowUseAndDate = sheet.createRow(useY);
            final XSSFCell useCell = rowUseAndDate.createCell(useX);
            final XSSFCell dateCell = rowUseAndDate.createCell(dateX);
            useCell.setCellStyle(useStyle);
            dateCell.setCellStyle(dateStyle);
            useCell.setCellValue(String.format("导出者：%s", useName));
            SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd HH:mm");//设置日期格式
            dateCell.setCellValue(df.format(new Date()));

            //写入列头
            final int autoNo = noSign ? 1 : 0;
            XSSFRow rowColumnName = sheet.createRow(colY);
            for (int j = 0; j < dataLength; j++) {
                XSSFCell cell = rowColumnName.createCell(colX + j);
                String columnName;
                if (noSign) {
                    if (j == 0)
                        columnName = "序号";
                    else
                        columnName = columnModels[j - autoNo].getColumnName();
                } else
                    columnName = columnModels[j].getColumnName();
                cell.setCellValue(columnName);
                if (j == 0)
                    cell.setCellStyle(columnTitleFirst);
                else if (j == dataLength - 1)
                    cell.setCellStyle(columnTitleLast);
                else
                    cell.setCellStyle(columnTitleMinute);
            }

            //写入数据
            final int dataRowCount = dataArray.length;
            for (int j = 0; j < dataRowCount; j++) {
                XSSFRow rowCurrent = sheet.createRow(dataY + j);
                for (int k = 0; k < dataLength; k++) {
                    XSSFCell cell = rowCurrent.createCell(dataX + k);
                    if (noSign) {
                        if (k == 0) {
                            cell.setCellValue(j + 1);
                        } else {
                            this.WriteCellData(cell, dataArray[j][k - autoNo], columnModels[k - autoNo].getDataType());
                        }
                    } else {
                        this.WriteCellData(cell, dataArray[j][k - autoNo], columnModels[k - autoNo].getDataType());
                    }
                    byte boardType = 0;
                    if (j < dataRowCount - 1) {
                        if (k == 0)
                            boardType = -4;
                        if (k == dataLength - 1)
                            boardType = 4;
                    } else {
                        if (!totalSign) {
                            if (k == 0)
                                boardType = -2;
                            if (k == dataLength - 1)
                                boardType = -3;
                            if (k > 0 && k < dataLength - 1)
                                boardType = -1;
                        } else {
                            if (k == 0)
                                boardType = -4;
                            if (k == dataLength - 1)
                                boardType = 4;
                        }
                    }
                    XSSFCellStyle dataStyle = getDataStyle(noSign ? (byte) 0 : columnModels[k - autoNo].getTextSite(), boardType, j);
                    cell.setCellStyle(dataStyle);
                }
            }

            //写入合计栏
            if (totalSign) {
                byte boardType = 0;
                XSSFCellStyle dataStyle;
                final XSSFRow rowTotal = sheet.createRow(dataY + dataRowCount);
                XSSFCell cellTotal;
                if (noSign) {
                    boardType = -2;
                    cellTotal = rowTotal.createCell(dataX);
                    cellTotal.setCellValue("合计");
                    dataStyle = getDataStyle((byte) 0, boardType, dataRowCount);
                    cellTotal.setCellStyle(dataStyle);
                }
                for (int j = 0; j < totalArray.length; j++) {
                    cellTotal = rowTotal.createCell(dataX + j + autoNo);
                    this.WriteCellData(cellTotal, totalArray[j], columnModels[j].getDataType());

                    if (j + autoNo == 0)
                        boardType = -2;
                    if (j == totalArray.length - 1)
                        boardType = -3;
                    if (j > autoNo * -1 && j < totalArray.length - 1)
                        boardType = -1;

                    dataStyle = getDataStyle(noSign ? (byte) 0 : columnModels[j].getTextSite(), boardType, dataRowCount);
                    cellTotal.setCellStyle(dataStyle);
                }

            }

            //写入说明
            if (remark != null) {
                final XSSFCellStyle remarkStyle = getRemark();
                XSSFRow rowRemark;
                if (totalSign)
                    rowRemark = sheet.createRow(dataY + dataRowCount + 1);
                else
                    rowRemark = sheet.createRow(dataY + dataRowCount);
                XSSFCell remarkCell = rowRemark.createCell(remarkX);
                remarkCell.setCellValue(remark);
                remarkCell.setCellStyle(remarkStyle);
            }

            //自适应列宽
            for (int j = titleX; j < dataLength; j++) {
                sheet.autoSizeColumn(j);
            }
        }

        final String name = tClass.getName();
        T resultValue;
        BufferedOutputStream bufferedOutPut = null;
        try {
            if ("java.lang.String".equals(name)) {
                String allPathFileName = String.format("%s%s%s"
                        , this.getPath()
                        , File.separator
                        , fileName);

                File createFolder = new File(allPathFileName);
                File parent = createFolder.getParentFile();
                if (!parent.exists())
                    parent.mkdirs();

                //文档输出
                FileOutputStream out = new FileOutputStream(createFolder);
                this.xssfWorkbook.write(out);
                out.close();
                resultValue = (T) allPathFileName;
            } else if ("java.lang.Void".equals(name)) {
                addExcelHead(response, fileName);
                ServletOutputStream outputStream = response.getOutputStream();
                bufferedOutPut = new BufferedOutputStream(outputStream);
                bufferedOutPut.flush();
                this.xssfWorkbook.write(bufferedOutPut);
                outputStream.flush();
                resultValue = (T) Void.class;
            } else {
                addExcelHead(response, fileName);
                ServletOutputStream outputStream = response.getOutputStream();
                bufferedOutPut = new BufferedOutputStream(outputStream);
                bufferedOutPut.flush();
                this.xssfWorkbook.write(bufferedOutPut);
                outputStream.flush();
                resultValue = (T) Void.class;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            bufferedOutPut.close();
        }
        return resultValue;
    }

    private void addExcelHead(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("multipart/form-data");
        response.addHeader("Content-Disposition", "attachment; filename=\""
                + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
    }

    private void WriteCellData(XSSFCell cell, Object data, byte dataType) {
        if (data == null) {
            return;
        }
        switch (dataType) {
            case 0:
                cell.setCellValue(data.toString());
                break;
            case 1:
                cell.setCellValue(getMyData(data, int.class));
                break;
            case 2:
                cell.setCellValue(getMyData(data, double.class));
                break;
            default:
                cell.setCellValue(data.toString());
                break;
        }
    }

}
